package com.itheima.cs.dao;

import com.itheima.cs.entity.dbd.InternalTransfer;
import com.itheima.cs.entity.dyewm.MaterialInfo;
import com.itheima.cs.help.DbOpenHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MaterialDao extends DbOpenHelper {


    public List<MaterialInfo> searchMaterials(String keyword, String username) {
        List<MaterialInfo> list = new ArrayList<>();
        try {
            getConnection();
            // 原先视图是V_T_BD_material_0
            String viewName = "V_T_BD_material_1";
            if ("王艳".equals(username)) {
                // 如果当前登录用户是王艳，则查询的物料视图修改，只查询本年度生产订单里面存在的物料
                viewName = "V_T_BD_material_1_dz";
            }
            String sql = "SELECT "
                    + "FNUMBER AS material_code, "
                    + "FNAME AS material_name, "
                    + "FSPECIFICATION AS material_specifications "
                    + "FROM [192.168.16.200].[AIS20201110160022].[dbo].["+viewName+"] "
                    + "WHERE FUSEORGID = 100008 "
                    + "AND (FNUMBER LIKE ? OR FNAME LIKE ? OR FSPECIFICATION LIKE ?)";
            pStmt = conn.prepareStatement(sql);
            String searchParam = "%" + keyword + "%";
            pStmt.setString(1, searchParam);
            pStmt.setString(2, searchParam);
            pStmt.setString(3, searchParam);

            rs = pStmt.executeQuery();
            while (rs.next()) {
                MaterialInfo info = new MaterialInfo(
                        rs.getString("material_code"),
                        rs.getString("material_name"),
                        rs.getString("material_specifications")
                );
                list.add(info);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    public MaterialInfo getMaterialByCode(String materialCode) {
        MaterialInfo info = null;
        try {
            getConnection();
            String sql = "SELECT FNUMBER AS material_code, "
                    + "FNAME AS material_name, "
                    + "FSPECIFICATION AS material_specifications "
                    + "FROM [192.168.16.200].[AIS20201110160022].[dbo].[V_T_BD_material_0] "
                    + "WHERE FUSEORGID = 100008 AND FNUMBER = ?";

            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, materialCode);

            rs = pStmt.executeQuery();
            if (rs.next()) {
                info = new MaterialInfo(
                        rs.getString("material_code"),
                        rs.getString("material_name"),
                        rs.getString("material_specifications")
                );
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return info;
    }


    /**
     * 根据物料编码、UUID和调入、调出仓库判断当前调拨唯一性，一框货调入调出一个仓库只能有一次
     * @param materialCode 物料编码
     * @param dbdUUID UUID
     * @param outWarehouseNo 调出仓库编号
     * @param inWarehouseNo 调入仓库编号
     */
    public List<InternalTransfer> getDBDList(String materialCode, String dbdUUID, String outWarehouseNo, String inWarehouseNo) {
        List<InternalTransfer> internalTransferList = new ArrayList<>();
        try {
            getConnection();
            String sql = "select id, transfer_number, material_code, transfer_id, dbd_uuid " +
                    "from [dbo].[dbd] where material_code = ? and dbd_uuid = ? and out_warehouse_no = ? and in_warehouse_no = ? " +
                    " and transfer_id is not null";

            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, materialCode);
            pStmt.setString(2, dbdUUID);
            pStmt.setString(3, outWarehouseNo);
            pStmt.setString(4, inWarehouseNo);

            rs = pStmt.executeQuery();
            if (rs.next()) {
                InternalTransfer internalTransfer = new InternalTransfer();
                internalTransfer.setId(rs.getInt("id"));
                internalTransfer.setTransferNumber(rs.getString("transfer_number"));
                internalTransfer.setMaterialCode(rs.getString("material_code"));
                internalTransfer.setTransferId(rs.getString("transfer_id"));
                internalTransfer.setDbdUUID(rs.getString("dbd_uuid"));
                internalTransferList.add(internalTransfer);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return internalTransferList;
    }
}